﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using quanlynhasach;
namespace quanlynhasach.Linksql
{

    class HangHoasql
    {
        static Connection connect = new Connection();
        public DataTable GetHangHoa()
        {

            return connect.getDS("SELECT Sach.MaSach,Sach.TenSach,SACH.GiaMua,Sach.GiaBan, Sach.DonVi, Sach.Thue, Sach.SoLuong, NhaCungCap.TenNCC, TheLoai.TenTL, Sach.TenTG FROM Sach INNER JOIN TheLoai  ON TheLoai.MaTheLoai = Sach.MaTL INNER JOIN  NhaCungCap ON NhaCungCap.MaNhaCungCap = Sach.NhaCungCap where MaSach <> 'N/A'"); 
        }

        public DataTable GetHangHoa(string sql)
        {
           return connect.getDS(sql);
        }



        public bool XoaHangHoa(string masach)
        {
            string[] param = { "@MaSach" };
            object[] value = { masach };
            string query = "Delete from Sach where MaSach = @MaSach";
            return connect.ExecuteNonQueryPara(query, param, value);
        }

        public bool SuaHangHoa(HangHoa hh)
        {
            DataTable dt = new DataTable();
            string MaTL="";
            string MaNCC="";
            dt = GetHangHoa("select MaTheLoai from TheLoai where TenTL = '" + hh.MaTL + "'");
            if (dt.Rows.Count > 0)
                MaTL = dt.Rows[0]["MaTheLoai"].ToString();
            
            dt = GetHangHoa("select MaNhaCungCap from NhaCungCap where TenNCC = '" + hh.MaNCC + "'");
            if (dt.Rows.Count > 0) 
                MaNCC = dt.Rows[0]["MaNhaCungCap"].ToString();
          
            string[] param ={"@MaSach","@TenSach","@GiaBan","@GiaMua","@TenTG","@MaTL","@TenNCC","@SoLuong", "@Thue", "@DonVi" };
            object[] value = {hh.MaHangHoa, hh.TenHangHoa, hh.GiaBan, hh.GiaMua, hh.TenTG, MaTL, MaNCC, hh.SoLuong, hh.Thue, hh.DonVi };
            string query = "Update Sach"
                           + " set MaSach = @MaSach ,TenSach=@TenSach,GiaBan = @GiaBan,GiaMua = @GiaMua,TenTG=@TenTG ,MaTL = @MaTL, NhaCungCap = @TenNCC, SoLuong = @SoLuong, Thue = @Thue, DonVi = @DonVi WHERE MaSach = @MaSach";
            return connect.ExecuteNonQueryPara(query, param, value);
        }

        public bool ThemHangHoa(HangHoa hh)
        {
            DataTable dt = new DataTable();
            string MaTL = "";
            string MaNCC = "";
            dt = GetHangHoa("select MaTheLoai from TheLoai where TenTL = '" + hh.MaTL + "'");
            if (dt.Rows.Count > 0)
                MaTL = dt.Rows[0]["MaTheLoai"].ToString();

            dt = GetHangHoa("select MaNhaCungCap from NhaCungCap where TenNCC = '" + hh.MaNCC + "'");
            if (dt.Rows.Count > 0)
                MaNCC = dt.Rows[0]["MaNhaCungCap"].ToString();
            string[] param = { "@MaSach", "@TenSach", "@GiaBan", "@GiaMua", "@TenTG", "@MaTL", "@TenNCC", "@SoLuong", "@Thue", "@DonVi" };
            object[] value = { hh.MaHangHoa, hh.TenHangHoa, hh.GiaBan, hh.GiaMua, hh.TenTG, MaTL,MaNCC, hh.SoLuong, hh.Thue, hh.DonVi };
            string query = "Insert into Sach"
                           + "(MaSach, TenSach, GiaMua, GiaBan, NhaCungCap, SoLuong, MaTL, TenTG, Thue, DonVi)"
                           + " values (@MaSach, @TenSach, @GiaMua, @GiaBan, @TenNCC, @SoLuong, @MaTL, @TenTG, @Thue, @DonVi)";
            return connect.ExecuteNonQueryPara(query, param, value);
        }

        public string SearchDaTaGrid()
        {
            int Count = 0;
            string MaNV = "";
            DataTable dt = new DataTable();//tao bang tam de luu
            dt = connect.getDS("Select MaSach From Sach");
            for (int i = 1; true; i++)
            {
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    if (dt.Rows[j]["MaSach"].ToString() != "S" + i)
                    {
                        Count++;//dem so lan khac
                    }
                    else
                    {
                        Count = 0;
                        break;
                    }
                }
                if (Count == dt.Rows.Count)// new so lan khac bang so hang cua bang, nghia la khong co dong nao trung thi tu dong add
                {
                    MaNV = "S" + i;
                    break;
                }
            }
            return MaNV;
        }
    }
}
